package com.tcfvic.server.upload;

import java.io.BufferedOutputStream;
import java.io.DataOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Cell;
import jxl.DateCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.ProgressListener;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.log4j.Logger;
import org.datanucleus.util.StringUtils;

import com.tcfvic.client.PersonService;
import com.tcfvic.client.pojos.Address;
import com.tcfvic.client.pojos.Course;
import com.tcfvic.client.pojos.Employmentdetails;
import com.tcfvic.client.pojos.EmploymentdetailsId;
import com.tcfvic.client.pojos.Expense;
import com.tcfvic.client.pojos.ExpenseId;
import com.tcfvic.client.pojos.Fundingsource;
import com.tcfvic.client.pojos.Person;
import com.tcfvic.client.pojos.Personaddress;
import com.tcfvic.client.pojos.Personcourse;
import com.tcfvic.client.pojos.PersoncourseId;
import com.tcfvic.client.pojos.Personcoursefundingsource;
import com.tcfvic.client.pojos.PersoncoursefundingsourceId;
import com.tcfvic.client.pojos.Personfundingsource;
import com.tcfvic.client.pojos.PersonfundingsourceId;
import com.tcfvic.client.pojos.Rto;
import com.tcfvic.client.pojos.Workercompany;
import com.tcfvic.server.PersonServiceImpl;

public class ExcelHoustonFormat extends HttpServlet implements IExcelFormat {
	private static final long serialVersionUID = 5567237221724423872L;

	private static final Logger logger = Logger
			.getLogger(ExcelHoustonFormat.class);

	private final String PACIFIC_BRANDS_NAME = "Pacific Brands";

	private List<String> errorList = new ArrayList<String>();
	private int numberImported = 0;

	private Map<Integer, Person> persons;
	private Map<String, Course> courses;
	private Map<String, Fundingsource> fundingSources;
	private Map<String, Workercompany> companies;
	private PersonService personService = new PersonServiceImpl();
	private Map<String, Rto> rtos;

	private final String KAYSER_NAME = "Kayser";
	private final String VIC_GOV_NAME = "Vic Government";
	private final String SKILL_UP_NAME = "Skill up";
	private final String JULIA_NAME = "Julia";
	private final String TCFSAP_NAME = "TCF-SAP";
	private final String FED_GOV_NAME = "Federal Government";
	private final String SAP_PPP_NAME = "SAP-PPP (FREE Cert III course)";

	public ExcelHoustonFormat() {
		logger.info("Uploading a Houston file format.");
		personService = new PersonServiceImpl();
		initialise();
	}

	@Override
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		response.setContentType("text/plain");
		logger.info("Inside Houston File Upload Servlet...............>>>>>>>>>>");
		personService = new PersonServiceImpl();
		initialise();
		try {
			getFileItem(request);
		} catch (Exception e) {
			logger.error(e);
		}
	}

	@SuppressWarnings("unchecked")
	private FileItem getFileItem(final HttpServletRequest request) {
		FileItemFactory factory = new DiskFileItemFactory();
		ServletFileUpload upload = new ServletFileUpload(factory);
		// Create a progress listener, if want to use
		ProgressListener progressListener = new ProgressListener() {
			private long megaBytes = -1;
			int i = -1;

			public void update(long pBytesRead, long pContentLength, int pItems) {
				if (i != pItems) {
					i = pItems;
				}
				long mBytes = pBytesRead / 10000;
				if (megaBytes == mBytes) {
					return;
				}
				megaBytes = mBytes;
			}
		};
		upload.setProgressListener(progressListener);

		try {
			List items = upload.parseRequest(request);
			Iterator it = items.iterator();
			while (it.hasNext()) {
				FileItem item = (FileItem) it.next();
				if (!item.isFormField()) {
					this.processUpload(item);
				}
			}
		} catch (Exception e) {
			logger.error(e);
			return null;
		}
		return null;
	}

	private void processUpload(FileItem uploadItem) {
		try {
			String uploadDir = getServletContext().getRealPath("/") + "tcfuaUplaod/";
			File file = new File(uploadDir);
			file.mkdirs();

			InputStream is = uploadItem.getInputStream();
			String filename = String.valueOf(System.currentTimeMillis()) + ".xls";
			File uploadedFile = new File(uploadDir + "/" + filename);
			DataOutputStream out = null;
			out = new DataOutputStream(new BufferedOutputStream(
					new FileOutputStream(uploadedFile)));
			while (true) {
				int value = is.read();
				out.write(value);
				if (value < 0) {
					break;
				}
			}
			out.close();
			uploadFile(uploadedFile);

		} catch (IOException e) {
			logger.error(e);
		}
	}

	public void initialise() {
		List<Course> result = personService.getAllTraining();
		courses = new HashMap<String, Course>();
		for (Course course : result) {
			courses.put(course.getName(), course);
		}

		List<Person> personResultList = personService.getAllPersonDetails();
		persons = new HashMap<Integer, Person>();
		for (Person person : personResultList) {
			persons.put(person.getId(), person);
		}

		List<Rto> rtoResult = personService.getAllRTOs();
		rtos = new HashMap<String, Rto>();
		for (Rto rto : rtoResult) {
			rtos.put(rto.getName(), rto);
		}

		List<Workercompany> wcResultList = personService.getAllCompanies();
		companies = new HashMap<String, Workercompany>();
		for (Workercompany company : wcResultList) {
			companies.put(company.getName() + company.getSite(), company);
		}

		List<Fundingsource> allFundingSources = personService
				.getAllFundingSources();
		fundingSources = new HashMap<String, Fundingsource>();
		for (Fundingsource fs : allFundingSources) {
			fundingSources.put(fs.getSourceName(), fs);
		}
	}

	public static void main(String... args) {
		ExcelHoustonFormat f = new ExcelHoustonFormat();
		f.uploadFile(new File(
				"F:/Dev/daves79/trunk/TCFVic/Pac Brands - All Data - Cessnock.xls"));
	}

	public void uploadFile(File file) {
		logger.info("In ExcelTCVVicFormat.uploadFile");

		try {
			Workbook workbook = createWorkbook(file);
			if (workbook.getSheets() != null) {
				processSheets(workbook);
			}
		} catch (Exception e) {
			logger.error(e);
		}
	}

	public void processSheets(Workbook workbook) throws Exception {
		Sheet sheet = workbook.getSheet(0);
		int i = 3;
		while (true) {
			i++;
			Person person = null;
			if (sheet.getCell("B" + i) != null
					&& !sheet.getCell("B" + i).getContents().isEmpty()) {
				try {
					String personNameAlreadyInDatabase = getNameIfPersonExists(sheet
							.getRow(i - 1));
					if (personNameAlreadyInDatabase != null) {
						String msg = "Person not imported as they already exist - "
								+ personNameAlreadyInDatabase;
						errorList.add(msg);
						logger.warn(msg);
						continue;
					}

					try {
						person = processPersonRecord(sheet.getRow(i - 1));
					} catch (Exception e) {
						String msg = "Sheet not processed due to: "
								+ e.getMessage();
						errorList.add(msg);
						logger.warn(msg);
						continue;
					}

					processFundingSources(person);
					person.setEmploymentdetailses(processCompany(sheet
							.getRow(i - 1), person));
					person.setPersoncourses(processPersonCourses(sheet
							.getRow(i - 1), person));
					processExpense(sheet.getRow(i - 1), person);

					personService.savePersonDetails(person);
					numberImported++;
				} catch (Exception e) {
					logger.error("An error occured importing data for "
							+ person.getFirstname() + ", "
							+ person.getSurname(), e);
				}
			} else {
				break;
			}
		}
	}

	private Set<Employmentdetails> processCompany(Cell[] row, Person person)
			throws Exception {
		// Company details
		String employeeNo = row[4].getContents();
		String companySite = row[0].getContents();
		String division = "";

		if (!companies.containsKey(PACIFIC_BRANDS_NAME + companySite)) {
			Workercompany company = new Workercompany();
			company.setName(PACIFIC_BRANDS_NAME);
			company.setSite(companySite);
			personService.saveCompanyDetails(company);
			initialise();
		}
		String companySiteName = companySite;
		if (companySiteName == null) {
			companySiteName = "";
		}
		Set<Employmentdetails> employmentdetailsSet = new HashSet<Employmentdetails>();
		Workercompany company = companies.get(PACIFIC_BRANDS_NAME
				+ companySiteName);
		Employmentdetails empDetails = new Employmentdetails();
		empDetails.setId(new EmploymentdetailsId(company.getId(), person
				.getId()));
		if (StringUtils.isEmpty(employeeNo)) {
			logger.warn("While importing from file, " + person.getFirstname()
					+ " " + person.getSurname()
					+ " has no employee number registerd");
			errorList.add(person.getFirstname() + " " + person.getSurname()
					+ " has no employee number registerd");
		}
		empDetails.setEmployeenumber(employeeNo);
		empDetails.setDivision(division);
		employmentdetailsSet.add(empDetails);
		person.setEmploymentdetailses(employmentdetailsSet);

		return employmentdetailsSet;
	}

	private Set<Personcourse> processPersonCourses(Cell[] row, Person person)
			throws Exception {
		Set<Personcourse> pcSet = new HashSet<Personcourse>();
		int startCol = 15;
		int courseCount = 0;
		while (true) {
			if (row[startCol] == null || row[startCol].getContents().isEmpty()
					|| courseCount == 6) {
				break;
			}
			String courseName = row[startCol++].getContents().trim();
			String courseType = row[startCol++].getContents().trim();
			String rtoName = row[startCol++].getContents().trim();
			Cell startDate = row[startCol++];
			Cell endDate = row[startCol++];
			String cost = row[startCol++].getContents().trim();
			String pacBrandsPayment = row[startCol++].getContents().trim();
			String tcfSAPPayment = row[startCol++].getContents().trim();
			String SAP_PPP = row[startCol++].getContents().trim();
			String pbgDays = row[startCol++].getContents().trim();
			String pbgHours = row[startCol++].getContents().trim();

			Course course = processCourse(courseName, courseType, rtoName, cost);

			// Fundingsource fs = fundingSources.get();

			Personcourse pc = new Personcourse();
			pc.setRto(processRTO(rtoName));
			// pc.setCourse(course);
			try {
				pc.setTrainingdate(((DateCell) startDate).getDate());
			} catch (Exception e) {
				logger.info("  Ok to ignore: " + person.getFirstname() + " "
						+ person.getSurname()
						+ ": Training start date could not be extracted - "
						+ startDate.getContents());
			}
			try {
				pc.setFinishDate(((DateCell) endDate).getDate());
			} catch (Exception e) {
				logger.info("  Ok to ignore: " + person.getFirstname() + " "
						+ person.getSurname()
						+ ": Training finish date could not be extracted - "
						+ endDate.getContents());
			}

			// Create PCFS list
			Set<Personcoursefundingsource> pcfsSet = new HashSet<Personcoursefundingsource>();

			// 1st PCFS
			processPCFS(pacBrandsPayment, pbgDays, pbgHours, pc, pcfsSet,
					PACIFIC_BRANDS_NAME, KAYSER_NAME, course);
			// processPCFS(skillUpPayment, pbgDays, pbgHours, pc, pcfsSet,
			// VIC_GOV_NAME, SKILL_UP_NAME, course);
			processPCFS(tcfSAPPayment, pbgDays, pbgHours, pc, pcfsSet,
					JULIA_NAME, TCFSAP_NAME, course);
			processPCFS(SAP_PPP, pbgDays, pbgHours, pc, pcfsSet, FED_GOV_NAME,
					SAP_PPP_NAME, course);

			pc.setPersoncoursefundingsources(pcfsSet);
			pc.setId(new PersoncourseId(0, person.getId(), course.getId()));
			pcSet.add(pc);
			courseCount++;
		}
		return pcSet;
	}

	private Rto processRTO(String rtoName) throws Exception {
		if (rtos.containsKey(rtoName)) {
			return rtos.get(rtoName);
		} else {
			Rto rto = new Rto();
			rto.setName(rtoName);
			personService.saveRTO(rto);
			initialise();
			return rtos.get(rtoName);
		}
	}

	private void processExpense(Cell[] row, Person person) throws Exception {

		List<Expense> expenses = new ArrayList<Expense>();
		int startCol = 81;
		int counter = 1;
		while (true) {
			if ((startCol + 5) >= row.length || row[startCol] == null
					|| row[startCol].getContents() == null
					|| row[startCol].getContents().isEmpty()) {
				break;
			}
			String desc = row[startCol++].getContents();
			Cell amount = row[startCol++];
			String course = row[startCol++].getContents();
			String fundingSource = row[startCol++].getContents();
			Cell date = row[startCol++];
			Expense expense = new Expense();
			expense.setId(new ExpenseId(counter++, person.getId()));
			expense.setDescription(desc);
			expense.setRelatedCourse(course);
			try {
				expense.setAmount(((NumberCell) amount).getValue());
			} catch (Exception e) {
				logger.error(e);
			}
			try {
				expense.setFundingsource(getFundingSource(fundingSource, null));
			} catch (Exception e) {
			}
			try {
				expense.setDate(((DateCell) date).getDate());
			} catch (Exception e) {
				logger.info("  Ok to ignore: " + person.getFirstname() + " "
						+ person.getSurname()
						+ ": Expense date could not be extracted - "
						+ date.getContents());
			}
			expense.setPerson(person);
			expenses.add(expense);
		}
		personService.saveExpenses(expenses, true);
	}

	private void processPCFS(String cost, String pbgDays, String pbgHours,
			Personcourse pc, Set<Personcoursefundingsource> pcfsSet,
			String sourceName, String businessName, Course course) {
		if (cost != null && cost.trim().length() > 0) {
			Personcoursefundingsource pcfs = new Personcoursefundingsource();
			Fundingsource fs = getFundingSource(sourceName, businessName);
			pcfs.setFundingsource(fs);
			pcfs.setPersoncourse(pc);
			try {
				pcfs.setCost(Double.valueOf(cost));
			} catch (NumberFormatException e) {
			}
			if (sourceName.equals(PACIFIC_BRANDS_NAME)) {
				try {
					pcfs.setTrainingdaysclaimed(Integer.valueOf(pbgDays));
				} catch (NumberFormatException e) {
				}
				try {
					pcfs.setTraininghourclaimed(Integer.valueOf(pbgHours));
				} catch (NumberFormatException e) {
				}
			}

			pcfs.setId(new PersoncoursefundingsourceId(fs.getId(), 0, 0, course
					.getId()));
			pcfsSet.add(pcfs);
		}
	}

	private Fundingsource getFundingSource(String sourceName,
			String businessName) {
		for (Fundingsource fsCheck : fundingSources.values()) {
			if (/*
				 * fsCheck.getBusinessName().equals(businessName) &&
				 */fsCheck.getSourceName().equals(sourceName)) {
				return fsCheck;
			}
		}
		return null;
	}

	private void processFundingSources(Person person) throws Exception {
		Set<Personfundingsource> pfsSet = new HashSet<Personfundingsource>();

		Fundingsource fs = insertFundingSource(PACIFIC_BRANDS_NAME, KAYSER_NAME);
		pfsSet.add(saveConnectPersonFundingSource(fs, person));

		fs = insertFundingSource(VIC_GOV_NAME, SKILL_UP_NAME);
		pfsSet.add(saveConnectPersonFundingSource(fs, person));

		fs = insertFundingSource(JULIA_NAME, TCFSAP_NAME);
		pfsSet.add(saveConnectPersonFundingSource(fs, person));

		fs = insertFundingSource(FED_GOV_NAME, SAP_PPP_NAME);
		pfsSet.add(saveConnectPersonFundingSource(fs, person));

		person.setPersonfundingsources(pfsSet);
	}

	private Fundingsource insertFundingSource(String sourceName,
			String businessName) throws Exception {
		Fundingsource fs = new Fundingsource();
		fs.setSourceName(sourceName);
		fs.setBusinessName(businessName);
		// Set<Personfundingsource> pfsSet = new HashSet<Personfundingsource>();
		// check if FS doesn't already exist
		boolean fsExists = false;
		for (Fundingsource fsCheck : fundingSources.values()) {
			if (/* fsCheck.getBusinessName().equals(fs.getBusinessName()) */
			fsCheck.getSourceName().equals(fs.getSourceName())) {
				fsExists = true;
			}
		}
		if (!fsExists) {
			personService.saveFundingSourceDetails(fs);
		}
		initialise();
		return getFundingSource(fs.getSourceName(), fs.getBusinessName());
	}

	private Personfundingsource saveConnectPersonFundingSource(
			Fundingsource fs, Person person) {
		Personfundingsource pfs = new Personfundingsource();
		pfs.setId(new PersonfundingsourceId(fs.getId(), person.getId()));
		pfs.setInitialamount(fs.getInitialamount());
		return pfs;
	}

	private Course processCourse(String courseName, String courseType,
			String rtoName, String cost) throws Exception {
		// get course or create course
		Course course = courses.get(courseName);
		if (course == null) {
			course = new Course();
			course.setName(courseName.trim());
			course.setCourseType(courseType.trim());
			try {
				course.setCost(Double.valueOf(cost));
			} catch (Exception e) {
			}
			personService.saveTrainingDetails(course);
			initialise();
			course = courses.get(courseName);
		}
		return course;
	}

	private String getNameIfPersonExists(Cell[] row) {
		String surname = fullCamelCase(row[3].getContents());
		String firstname = fullCamelCase(row[2].getContents());

		for (Person person : persons.values()) {
			if (person.getSurname().equals(surname)
					&& person.getFirstname().equals(firstname)) {
				return "Firstname: " + person.getFirstname() + ", Surname: "
						+ person.getSurname();
			}
		}
		return null;
	}

	private static String capitalize(String s) {
		if (s.length() == 0)
			return s;
		return s.substring(0, 1).toUpperCase() + s.substring(1).toLowerCase();
	}

	private String fullCamelCase(String camelMe) {
		StringBuffer sb = new StringBuffer();
		String[] words = camelMe.split("\\s");
		for (String s : words) {
			sb.append(capitalize(s));
		}
		return sb.toString();
	}

	private Person processPersonRecord(Cell[] row) throws Exception {
		String cellSurname = fullCamelCase(row[3].getContents().trim());
		String cellFirstname = fullCamelCase(row[2].getContents().trim());
		String cellStreetaddress = row[6].getContents().trim();
		String cellSuburb = row[7].getContents().trim();
		String cellState = row[8].getContents().trim();
		String cellPostcode = row[9].getContents().trim();
		String cellPhone = row[12].getContents().trim();
		String cellMobile = row[13].getContents().trim();
		String cellEmail = row[14].getContents().trim();
		// String cellUnionMemberNo = row[4].getContents();

		if (cellFirstname == null || cellFirstname.isEmpty()) {
			throw new Exception("Row is missing given name");
		}

		if (cellSurname == null || cellSurname.isEmpty()) {
			throw new Exception("Row is missing surname");
		}

		logger.info("Importing " + cellFirstname + " " + cellSurname);

		Person person = new Person();
		Address address = new Address();
		if (cellSurname != null) {
			person.setSurname(cellSurname);
		}
		if (cellFirstname != null) {
			person.setFirstname(cellFirstname);
		}
		if (cellStreetaddress != null) {
			address.setStreet(cellStreetaddress);
		}
		if (cellSuburb != null) {
			address.setSuburb(cellSuburb);
		}
		if (cellState != null) {
			address.setState(cellState);
		}
		if (cellPostcode != null) {
			try {
				address.setPostcode(new Integer(cellPostcode));
			} catch (Exception e) {
			}
		}
		if (cellPhone != null) {
			person.setLandline(cellPhone);
		}
		if (cellMobile != null) {
			person.setMobile(cellMobile);
		}
		if (cellEmail != null) {
			person.setEmail(cellEmail);
		}
		// if (cellUnionMemberNo != null) {
		// person.setUnionMemberNo(cellUnionMemberNo);
		// }
		Personaddress personAddress = new Personaddress();
		personAddress.setAddress(address);
		Set<Personaddress> personAddresses = new HashSet<Personaddress>();
		personAddresses.add(personAddress);
		person.setPersonaddresses(personAddresses);

		person = personService.savePersonDetails(person);
		return person;
	}

	public Workbook createWorkbook(File file) throws BiffException, IOException {
		return Workbook.getWorkbook(file);
	}

}
